package com.yuantu.judiciary.dao;

import com.yuantu.judiciary.model.domain.QueryRecordDO;
import com.yuantu.judiciary.model.enums.AuthResultType;
import com.yuantu.judiciary.model.enums.EndpointType;
import com.yuantu.judiciary.model.enums.SearchType;
import com.yuantu.judiciary.model.vo.QuerySearchVO;
import com.yuantu.judiciary.model.vo.QuerySourceSearchVO;
import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;
import java.util.Set;

/**
 * 查询记录数据库操作接口
 *
 * @author syw
 * @since 2023-07-06
 **/
public interface IQueryRecordDao extends JpaRepository<QueryRecordDO, Long> {

    QueryRecordDO findByCreatorIdAndTypeAndContentAndDeletedFalse(Long creatorId, SearchType type, String content);

    @Query("SELECT q FROM QueryRecordDO q WHERE q.creatorId = :creatorId " +
            "AND q.content = :content AND q.deleted = false " +
            "AND q.createTime >= :startTime")
    List<QueryRecordDO> findByCreatorIdAndContentAndDeletedFalseWithin5Minutes(
            @Param("creatorId") Long creatorId,
            @Param("content") String content,
            @Param("startTime") Date startTime);
    QueryRecordDO findByIdAndDeletedFalse(Long id);

    Page<QueryRecordDO> findPageByCreatorIdAndTypeAndDeletedFalse(Long creatorId, SearchType type, Pageable pageable);

    @Query("select q from QueryRecordDO q where (q.creatorId = :creatorId or q.example = true) and q.type = :type and q.deleted = false and q.show = true")
    Page<QueryRecordDO> findPageByCreatorIdAndTypeWithExample(Long creatorId, SearchType type, Pageable pageable);

    @Query("select q from QueryRecordDO q where q.creatorId = :creatorId and q.type = :type and q.deleted = false and q.show = true")
    Page<QueryRecordDO> findPageByCreatorIdAndType(Long creatorId, SearchType type, Pageable pageable);

    @Query("select q from QueryRecordDO q where (q.creatorId = :creatorId or q.example = true) and q.type = :type and q.origin = 'ALIPAY_APPLET' and q.deleted = false")
    Page<QueryRecordDO> findPageByCreatorIdAndTypeWithExampleByAli(Long creatorId, SearchType type, Pageable pageable);

    @Query("select q from QueryRecordDO q where (q.creatorId = :creatorId or q.example = true) and q.type = :type and q.origin <> 'ALIPAY_APPLET' and q.deleted = false")
    Page<QueryRecordDO> findPageByCreatorIdAndTypeWithExampleWithoutAli(Long creatorId, SearchType type, Pageable pageable);

    List<QueryRecordDO> findByCreatorIdAndEntityIdInAndDeletedFalse(Long creatorId, List<Long> entityIdList);

    List<QueryRecordDO> findByCreatorIdAndTypeAndDeletedFalse(Long creatorId, SearchType type);

    List<QueryRecordDO> findByCreatorIdAndTypeAndDeletedFalseAndExampleFalse(Long creatorId, SearchType type);


    List<QueryRecordDO> findByCreatorIdAndTypeAndSelfAndDeletedFalseAndExampleFalse(Long userId, SearchType type, Boolean isShelf);

    List<QueryRecordDO> findByCreatorIdInAndTypeAndDeletedFalseAndExampleFalse(Set<Long> creatorId, SearchType type);

    List<QueryRecordDO> findByCreatorIdInAndTypeAndSelfAndDeletedFalseAndExampleFalse(Set<Long> creatorId, SearchType type, Boolean isShelf);

    QueryRecordDO findFirstByCreatorIdAndEntityIdAndDeletedFalse(Long creatorId, Long entityId);

    Integer countDistinctByCreatorIdAndAuthorizedResultAndDeletedFalse(Long creatorId, AuthResultType authorizedResult);

    Integer countDistinctByCreatorIdAndTypeAndDeletedFalse(Long creatorId, SearchType type);


    Page<QueryRecordDO> findPageByCreatorIdAndAuthorizedResultAndDeletedFalseAndTradeNoIsNull(Long creatorId, AuthResultType authorizedResult, Pageable pageable);

    Integer countDistinctByCreatorIdInAndAuthorizedResultAndDeletedFalse(List<Long> creatorId, AuthResultType authorizedResult);

    List<QueryRecordDO> findByCreatorIdInAndAuthorizedResultAndDeletedFalse(List<Long> creatorId, AuthResultType authorizedResult);

    @Query("select q from QueryRecordDO q " +
            "where q.creatorId in (:creatorId) and q.authorizedResult = :authorizedResult and q.deleted = false " +
            "and (:type is null or q.type = :type) ")
    Page<QueryRecordDO> findByCreatorIdInAndAuthorizedResultAndTypeAndDeletedFalse(List<Long> creatorId, AuthResultType authorizedResult, SearchType type, Pageable pageable);

    List<QueryRecordDO> findByTypeAndAuthorizedResultInAndExpiresTimeLessThanAndOriginAndDeletedFalse(SearchType type, List<AuthResultType> authorizedResult, Timestamp expiresTime, EndpointType origin);

    QueryRecordDO findByTradeNoAndDeletedFalse(String tradeNo);

    QueryRecordDO findFirstByCreatorIdAndTypeAndAuthorizedResultAndContentAndExpiresTimeGreaterThanEqualAndOriginAndSelfTrueAndDeletedFalseOrderByIdDesc(
            Long creatorId, SearchType type, AuthResultType authorizedResult, String content, Timestamp expiresTime, EndpointType origin
    );

    Integer countDistinctByCreatorIdInAndAuthorizedResultInAndDeletedFalse(List<Long> creatorId, List<AuthResultType> authorizedResult);

    Integer countDistinctByCreatorIdAndAuthorizedResultInAndCodeInfoInAndDeletedFalse(Long creatorId, List<AuthResultType> authorizedResult, List<String> codeInfo);

    @Query("select q from QueryRecordDO q " +
            "where q.creatorId in (:creatorId) and q.authorizedResult = 'AUTHORIZED' and q.deleted = false " +
            "and (:type is null or q.type = :type) and q.self = :self order by q.createTime desc")
    List<QueryRecordDO> findRecords(List<Long> creatorId, SearchType type, Boolean self, Pageable pageable);

    @Query("select q from QueryRecordDO q where q.creatorId = :creatorId and q.type = :type and q.deleted = false and q.show = true and q.self=:self")
    Page<QueryRecordDO> findSelfRecords(Long creatorId, SearchType type, Pageable pageable, Boolean self);

    @Query("select q from QueryRecordDO q where (q.creatorId = :creatorId or q.example = true) and q.type = :type and q.deleted = false and q.show = true and q.self=:self")
    Page<QueryRecordDO> findPageByCreatorIdAndTypeWithExampleForSelf(Long creatorId, SearchType type, Pageable pageable, Boolean self);

    List<QueryRecordDO> findByAuthorizedResultAndTradeNoIn(AuthResultType authResultType, List<String> tradeNoInfos);

    QueryRecordDO findByCreatorIdAndTypeAndContentAndSelfAndDeletedFalse(Long creatorId, SearchType type, String content, Boolean self);

    List<QueryRecordDO> findInfosByCreatorIdInAndIsFileAndExpiresTimeLessThanAndDeletedFalse(List<Long> creatorId, Boolean isFile, Timestamp expiresTime);

    @Query("select q from QueryRecordDO q left join SysFileDO f on q.id = f.relativeId where q.creatorId in :accountIds and q.isFile = :isFile and q.deleted = false " +
            "and q.type = :queryType and ((:isUpload = 1 and f.id is not null) or (:isUpload = 0 and f.id is null))")
    List<QueryRecordDO> findInfoByCreatorIdInAndTypeAndIsFileAndIsUploadAndDeletedFalse(List<Long> accountIds, SearchType queryType, Boolean isFile, Integer isUpload);


    @Query("select q from QueryRecordDO q left join AccountDO a on q.creatorId = a.id " +
            "left join UserInfoDO u on a.userId = u.id " +
            "left join OrganizationDO o on a.organizationId = o.id " +
            "where q.deleted = false and a.deleted = false and u.deleted = false " +
            "and (:#{#search.queryId} is null or q.id = :#{#search.queryId}) " +
            "and (:#{#search.querySource} is null or :#{#search.querySource} = '' or q.origin = :#{#search.querySource}) " +
            "and (:#{#search.queryType} is null or :#{#search.queryType} = '' or q.type = :#{#search.queryType}) " +
            "and (:#{#search.userName} is null or :#{#search.userName} = '' or a.username like concat('%',:#{#search.userName},'%')) " +
            "and (:#{#search.userPhone} is null or :#{#search.userPhone} = '' or u.phone like concat('%',:#{#search.userPhone},'%')) " +
            "and (:#{#search.isSource} is null or (:#{#search.isSource} = 1 and o.organizationSourceId != 1)) ")
    Page<QueryRecordDO> findQueryInfos(QuerySearchVO search, Pageable pageable);

    @Query("select q from QueryRecordDO q " +
            "left join SysFileDO f on q.id = f.relativeId " +
            "left join AccountDO a on q.creatorId = a.id " +
            "left join OrganizationDO o on a.organizationId = o.id " +
            "left join OrganizationSourceDO os on o.organizationSourceId = os.id " +
            "where q.deleted = false and a.deleted = false and o.deleted = false and os.deleted = false and q.isFile = true " +
            "and (:#{#search.queryId} is null or q.id = :#{#search.queryId}) " +
            "and (:#{#search.queryType} is null or :#{#search.queryType} = '' or q.type = :#{#search.queryType}) " +
            "and (:#{#search.orgName} is null or :#{#search.orgName} = '' or o.enterName like concat('%',:#{#search.orgName},'%')) " +
            "and (:#{#search.orgSource} is null or :#{#search.orgSource} = '' or os.orgSource like concat('%',:#{#search.orgSource},'%'))" +
            "and (:#{#search.uploadStatus} is null or (:#{#search.uploadStatus} = 0 and f.id is null) or (:#{#search.uploadStatus} = 1 and f.id is not null) ) " +
            "group by q.id ")
    Page<QueryRecordDO> findQueryAuthInfos(QuerySourceSearchVO search, Pageable pageable);

    @Modifying
    @Query("update QueryRecordDO set expiresTime = :expireTime, isFile = true where id = :queryId and deleted = false ")
    void updateQueryRecordAuth(Long queryId, Timestamp expireTime);

    QueryRecordDO findInfoByContentAndTypeAndCreatorId(String content, SearchType searchType, Long creatorId);

    @Query("SELECT COUNT(DISTINCT q) FROM QueryRecordDO q " +
            "WHERE q.deleted = false " +
            "AND q.authorizedResult in :authorizedResult " +
            "AND q.creatorId in :creatorId " +
            "AND q.tradeNo is null or q.tradeNo = ''")
    Integer countDistinctByCreatorIdInAndAuthorizedResultInAndDeletedFalseAndTradeNoIsEmptyOrTradeNoIsNull(List<Long> creatorId, List<AuthResultType> authorizedResult);


    @Query("SELECT COUNT(DISTINCT q) FROM QueryRecordDO q " +
            "left join VipOrderDO o on q.tradeNo = o.tradeNo " +
            "WHERE q.deleted = false " +
            "AND q.authorizedResult in :authorizedResult " +
            "AND q.creatorId in :creatorId " +
            "AND o.vipId in :vipIdList")
    Integer getH5QueryRecordCount(List<Long> creatorId, List<AuthResultType> authorizedResult, List<Long> vipIdList);

    @Query("SELECT q FROM QueryRecordDO q WHERE q.content LIKE %:keyword% and q.shopId !=null and q.deleted = false")
    List<QueryRecordDO> findByContentContaining(@Param("keyword") String keyword);

    Integer countDistinctByTradeNoAndDeletedFalse(String tradeNo);
}
